{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2\n",
      "timestamp = 2014-01-02D04:19:51.857166800\n",
      "-------------------------------------------\n",
      "index_find = 16\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/rory/anaconda2/lib/python2.7/site-packages/ipykernel/__main__.py:81: FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)\n",
      "/home/rory/anaconda2/lib/python2.7/site-packages/ipykernel/__main__.py:82: FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)\n",
      "/home/rory/anaconda2/lib/python2.7/site-packages/ipykernel/__main__.py:170: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/home/rory/anaconda2/lib/python2.7/site-packages/ipykernel/__main__.py:171: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/home/rory/anaconda2/lib/python2.7/site-packages/ipykernel/__main__.py:384: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/home/rory/anaconda2/lib/python2.7/site-packages/ipykernel/__main__.py:385: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/home/rory/anaconda2/lib/python2.7/site-packages/ipykernel/__main__.py:388: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/home/rory/anaconda2/lib/python2.7/site-packages/ipykernel/__main__.py:389: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/home/rory/anaconda2/lib/python2.7/site-packages/ipykernel/__main__.py:166: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/home/rory/anaconda2/lib/python2.7/site-packages/ipykernel/__main__.py:167: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total time = 5.539297\n"
     ]
    }
   ],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\"\"\"\n",
    "Created on Thu Dec 29 13:50:06 2016\n",
    "\n",
    "@author: rory\n",
    "\"\"\"\n",
    "\n",
    "import pandas as pd\n",
    "import time \n",
    "import csv\n",
    "\n",
    "def order_book_tranform(year,month,day,path,best_price_number,series):\n",
    "    ## read file\n",
    "    def read_file(year,month,day,path,series):\n",
    "        data = []\n",
    "        if len(str(month)) == 1:\n",
    "            month_ = '0' + str(month)\n",
    "        else:\n",
    "            month_ = str(month)\n",
    "        if len(str(day)) == 1:\n",
    "            day_ = '0' + str(day)\n",
    "        else:\n",
    "            day_ = str(day)\n",
    "        datapath = str(path) + str(year) + '.' + str(month_) + '.' + str(day_) + '.csv'\n",
    "        data = pd.read_csv(datapath)\n",
    "        data = data[data.Series == series]\n",
    "        return data.reset_index(drop = True)\n",
    "\n",
    "    def insert(order_book_data,data_to_insert,ob_position):\n",
    "        top = order_book_data[0:ob_position]\n",
    "        bottom = order_book_data[ob_position:]\n",
    "        return pd.concat((top,data_to_insert,bottom)).reset_index(drop = True)\n",
    "\n",
    "    def draw_out(order_book_data,ob_position):\n",
    "        top = order_book_data[0:ob_position]\n",
    "        bottom = order_book_data[ob_position + 1:]\n",
    "        return pd.concat((top,bottom)).reset_index(drop = True)\n",
    "\n",
    "    def order_book_to_csv(order_book_bid,order_book_ask,data,i):\n",
    "        \n",
    "        order_book_bid_sum = order_book_bid[['Price','QuantityDifference']].groupby(by = ['Price'],as_index = False,sort = False).sum()\n",
    "        order_book_ask_sum = order_book_ask[['Price','QuantityDifference']].groupby(by = ['Price'],as_index = False).sum()\n",
    "        order_book_bid_sum = order_book_bid_sum[order_book_bid_sum.QuantityDifference != 0.0].reset_index(drop = True)\n",
    "        order_book_ask_sum = order_book_ask_sum[order_book_ask_sum.QuantityDifference != 0.0].reset_index(drop = True)\n",
    "        order_book_bid_ask = pd.concat([order_book_bid_sum[['Price','QuantityDifference']],order_book_ask_sum[['Price','QuantityDifference']]],axis = 1)    \n",
    "       \n",
    "        with open('order_book_'+str(best_price_number)+'_'+str(year)+'_'+str(month)+'_'+str(day)+'.csv','a') as f:\n",
    "            order_book = csv.writer(f)\n",
    "            order_book.writerow([\"TimeStamp\",data.TimeStamp[i-1:i].iloc[0]])\n",
    "            order_book = csv.writer(f,delimiter=',')\n",
    "            for i in range(0,min(len(order_book_bid_ask),best_price_number),1):\n",
    "                order_book.writerow(order_book_bid_ask[i:i+1].values.tolist()[0])\n",
    "        return order_book_bid_sum,order_book_ask_sum\n",
    "\n",
    "    data = read_file(year,month,day,path,series)\n",
    "    \n",
    "    with open('order_book_' + str(best_price_number) + '_' + str(year) + '_' + str(month) + '_' + str(day) + '.csv', 'wb') as csvfile:\n",
    "        f = csv.writer(csvfile) \n",
    "\n",
    "    data[['QuantityDifference']] = data[['QuantityDifference']].astype(float)\n",
    "    data['QuantityDifference_'] = data['QuantityDifference']\n",
    "    data_ask = data[(data.BidOrAsk == 'A')].reset_index(drop=True)\n",
    "    data_bid = data[(data.BidOrAsk == 'B')].reset_index(drop=True)\n",
    "    order_book_bid = []\n",
    "    order_book_ask = []\n",
    "    x1 = data[(data.BidOrAsk == 'A')].TimeStamp.unique()\n",
    "    x2 = data[(data.BidOrAsk == 'B')].TimeStamp.unique()\n",
    "    temp_ask = 0\n",
    "    temp_bid = 0\n",
    "    \n",
    "    def first_order_create(index_,data):\n",
    "        timestamp = data.TimeStamp.unique()[index_]\n",
    "        print 'timestamp = %s'%(timestamp) \n",
    "        bid = []\n",
    "        ask = []\n",
    "        timestamp_ = []\n",
    "        index_find = data[data['TimeStamp'].str.contains(timestamp)].index[-1]\n",
    "        y = data[:index_find + 1]\n",
    "        bid.append(y[(y.BidOrAsk == 'B')][[\"Price\",\"OrderNumber\",\"QuantityDifference\",\"QuantityDifference_\"]]) # bid\n",
    "        ask.append(y[(y.BidOrAsk == 'A')][[\"Price\",\"OrderNumber\",\"QuantityDifference\",\"QuantityDifference_\"]]) # ask\n",
    "        a = bid[0].sort(['Price'],ascending = [False])\n",
    "        b = ask[0].sort(['Price'],ascending = [True])\n",
    "        order_book_bid = a[a.QuantityDifference != 0].reset_index(drop = True)\n",
    "        order_book_ask = b[b.QuantityDifference != 0].reset_index(drop = True)\n",
    "        order_book_bid_sum = order_book_bid[['Price','QuantityDifference']].groupby(by = ['Price'],as_index = False,sort = False).sum()\n",
    "        order_book_ask_sum = order_book_ask[['Price','QuantityDifference']].groupby(by = ['Price'],as_index = False).sum()\n",
    "        \n",
    "        if len(order_book_bid_sum[order_book_bid_sum.QuantityDifference == 0.0]) != 0 and len(order_book_ask_sum[order_book_ask_sum.QuantityDifference == 0.0]) != 0:\n",
    "            \n",
    "            print 'Exist Bid Ask Order Book Price = Zero'\n",
    "            price_bid_zero = order_book_bid_sum[order_book_bid_sum.QuantityDifference == 0.0]['Price'][0]\n",
    "            price_ask_zero = order_book_ask_sum[order_book_ask_sum.QuantityDifference == 0.0]['Price'][0]\n",
    "            order_book_bid = order_book_bid[order_book_bid.Price != price_bid_zero]\n",
    "            order_book_ask = order_book_ask[order_book_ask.Price != price_ask_zero]\n",
    "        elif len(order_book_bid_sum[order_book_bid_sum.QuantityDifference == 0.0]) != 0 and len(order_book_ask_sum[order_book_ask_sum.QuantityDifference == 0.0]) == 0:\n",
    "            \n",
    "            print 'Exist Bid Order Book Price = Zero'\n",
    "            price_bid_zero = order_book_bid_sum[order_book_bid_sum.QuantityDifference == 0.0]['Price'][0]\n",
    "            order_book_bid = order_book_bid[order_book_bid.Price != price_bid_zero]\n",
    "\n",
    "        elif len(order_book_bid_sum[order_book_bid_sum.QuantityDifference == 0.0]) == 0 and len(order_book_ask_sum[order_book_ask_sum.QuantityDifference == 0.0]) != 0:\n",
    "            \n",
    "            print 'Exist Ask Order Book Price = Zero'\n",
    "            price_ask_zero = order_book_ask_sum[order_book_ask_sum.QuantityDifference == 0.0]['Price'][0]\n",
    "            order_book_ask = order_book_ask[order_book_ask.Price != price_ask_zero]\n",
    "\n",
    "        order_book_bid_sum = order_book_bid_sum[order_book_bid_sum.QuantityDifference != 0].reset_index(drop = True)\n",
    "        order_book_ask_sum = order_book_ask_sum[order_book_ask_sum.QuantityDifference != 0].reset_index(drop = True)\n",
    "        order_book_bid_ask = pd.concat([order_book_bid_sum[['Price','QuantityDifference']],order_book_ask_sum[['Price','QuantityDifference']]],axis = 1)        \n",
    "        \n",
    "        return order_book_bid, order_book_ask, order_book_bid_ask, timestamp, y, index_find\n",
    "    \n",
    "    def with_first_order_book(best_price_number,year,month,day,timestamp,order_book_bid_ask,index_):\n",
    "        with open('order_book_'+str(best_price_number)+'_'+str(year)+'_'+str(month)+'_'+str(day)+'.csv','a') as f:  \n",
    "            order_book = csv.writer(f)\n",
    "            if index_ == 0:\n",
    "                order_book.writerow([\"Bid\",\"Bid_Quantity\",\"Ask\",\"Ask_Quantity\"])\n",
    "            order_book.writerow([\"TimeStamp\",timestamp])\n",
    "            order_book = csv.writer(f,delimiter=',')\n",
    "            for i in range(0,min(len(order_book_bid_ask),best_price_number),1):\n",
    "                order_book.writerow(order_book_bid_ask[i:i+1].values.tolist()[0])     \n",
    "    \n",
    "    # 建立初始委託簿\n",
    "    first_order_book_data_lenth = 0\n",
    "    order_book_bid_time = 0\n",
    "    order_book_ask_time = 0\n",
    "    \n",
    "    for time in range(0,1000,1):\n",
    "        index_ = time\n",
    "        order_book_bid, order_book_ask, order_book_bid_ask,\\\n",
    "        timestamp, y, index_find = first_order_create(index_, data)  \n",
    "\n",
    "        if len(order_book_bid) != 0 and len(order_book_ask) != 0:\n",
    "            with_first_order_book(best_price_number,year,month,day,timestamp,order_book_bid_ask,index_)  \n",
    "            break\n",
    "        elif len(order_book_bid) == 0 and len(order_book_ask) != 0: \n",
    "            with_first_order_book(best_price_number,year,month,day,timestamp,order_book_bid_ask,index_)\n",
    "            temp_ask +=1\n",
    "        elif len(order_book_bid) != 0 and len(order_book_ask) == 0:\n",
    "            with_first_order_book(best_price_number,year,month,day,timestamp,order_book_bid_ask,index_)\n",
    "            temp_bid +=1\n",
    "            \n",
    "    print '-------------------------------------------'\n",
    "    print 'index_find = %s'%(index_find)    \n",
    "    \n",
    "    for i in range(index_find + 1,100,1):#len(data), 1):\n",
    "        #print '---------------------------------'\n",
    "        #print data[['Price','QuantityDifference','BidOrAsk','TimeStamp']][i:i+1]\n",
    "        #print i ,temp_bid, temp_ask\n",
    "        #print data.TimeStamp[i], x2[temp_bid], x1[temp_ask]\n",
    "        time_second = int(data[i:i+1].TimeStamp.iloc[0][18]) + int(data[i:i+1].TimeStamp.iloc[0][17])*10 +\\\n",
    "                      int(data[i:i+1].TimeStamp.iloc[0][15])*60 + int(data[i:i+1].TimeStamp.iloc[0][14])*600 +\\\n",
    "                      int(data[i:i+1].TimeStamp.iloc[0][12])*3600 + int(data[i:i+1].TimeStamp.iloc[0][11])*36000\n",
    "\n",
    "        if time_second > 57600:\n",
    "            break\n",
    "        if time_second == 32400 and time_second >= 57300:\n",
    "            order_book_bid = order_book_bid.sort(['Price'],ascending = [False]).reset_index(drop = True)\n",
    "            order_book_ask = order_book_ask.sort(['Price'],ascending = [True]).reset_index(drop = True)\n",
    "            pass \n",
    "        \n",
    "        if data.BidOrAsk[i] == 'A':\n",
    "            data_ask_Quantity = data.BestQuantity[i]\n",
    "            if int(data[['QuantityDifference']][i:i+1].values) > 0 :\n",
    "                if order_book_bid.Price[0] >= data[i:i+1].Price.iloc[0] and time_second < 32400:\n",
    "                    for k in range(0,len(order_book_bid)):\n",
    "                        diff = order_book_bid.QuantityDifference_[k] - data[i:i+1].QuantityDifference_.iloc[0] \n",
    "                        if order_book_bid.Price[k] >= data[i:i+1].Price.iloc[0] and diff >= 0:\n",
    "                            order_book_bid.QuantityDifference_[k] = diff\n",
    "                            data[i:i+1].QuantityDifference_.iloc[0] = 0\n",
    "                            break\n",
    "                        elif order_book_bid.Price[k] >= data[i:i+1].Price.iloc[0] and diff < 0: \n",
    "                            order_book_bid.QuantityDifference_[k] = 0\n",
    "                            data[i:i+1].QuantityDifference_.iloc[0] = -diff\n",
    "                            pass\n",
    "                        else:\n",
    "                            break\n",
    "                if data.TimeStamp[i] == x1[temp_ask]:\n",
    "                    \n",
    "                    position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                    order_book_ask = insert(order_book_ask,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)\n",
    "                    if time_second > 32400 and time_second < 57300: \n",
    "                        if position_ == 0 and len(order_book_ask) > 1:   \n",
    "                            if order_book_ask[position_ + 1:position_ + 1 + 1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:  \n",
    "                                print 'Some error1(Ask & Q>0 & timestamp not change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            else:\n",
    "                                pass\n",
    "                        elif 0 < position_< (len(order_book_ask)-1):\n",
    "                            if order_book_ask[position_ + 1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_ask[position_ - 1:position_ - 1 + 1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:\n",
    "                                print 'Some error1(Ask & Q>0 & timestamp not change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            else:\n",
    "                                pass\n",
    "                        elif position_ == len(order_book_ask)-1:    \n",
    "                            if order_book_ask[position_ - 1:position_ - 1 + 1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:        \n",
    "                                print 'Some error1(Ask & Q>0 & timestamp not change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            else:\n",
    "                                pass\n",
    "                        elif position_ == 0 and len(order_book_ask) == 1:\n",
    "                            pass\n",
    "                    else:\n",
    "                        pass\n",
    "                elif data.TimeStamp[i] != x1[temp_ask]:\n",
    "                    if temp_ask == 0:\n",
    "                        temp_ask = temp_ask + 1\n",
    "                        best_price = data[i:(i+1)]['BestPrice']\n",
    "                        position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                        order_book_ask = insert(order_book_ask,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)                  \n",
    "\n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if position_ == 0 and len(order_book_ask) > 1:\n",
    "                                if order_book_ask[position_+1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:\n",
    "                                    print 'Some error2(Ask & Q>0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])     \n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif 0 < position_< len(order_book_ask)-1:\n",
    "                                if order_book_ask[position_+ 1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_ask[position_-1:position_-1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]: \n",
    "                                    print 'Some error2(Ask & Q>0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])    \n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif position_ == len(order_book_ask)-1: \n",
    "                                if order_book_ask[position_-1:position_-1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]: \n",
    "                                    print 'Some error2(Ask & Q>0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])      \n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif position_ == 0 and len(order_book_ask) == 1:\n",
    "                                pass\n",
    "                        else:\n",
    "                            pass\n",
    "                    else:\n",
    "                        order_book_bid_sum,order_book_ask_sum = order_book_to_csv(order_book_bid,order_book_ask,data,i)\n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) > 0.03 or\\\n",
    "                            round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) < 0:\n",
    "                                if data[i-1:i].BidOrAsk.iloc[0] == 'A':\n",
    "                                    if order_book_ask_sum[0:1].values.tolist()[0][1] == data[i-1:i].BestQuantity.iloc[0]:\n",
    "                                        pass\n",
    "                                    else:\n",
    "                                        print 'Best ask quantity is false'\n",
    "                                        pass\n",
    "                                        #break \n",
    "                                else:\n",
    "                                    j = i - 1\n",
    "                                    while j >= 1:\n",
    "                                        if data[j-1:j].BidOrAsk.iloc[0] == 'A':\n",
    "                                            if order_book_ask_sum[0:1].values.tolist()[0][1] == data[j-1:j].BestQuantity.iloc[0]:\n",
    "                                                break\n",
    "                                        else:\n",
    "                                            j = j - 1\n",
    "                                            pass\n",
    "                            else:\n",
    "                                pass\n",
    "                        else:\n",
    "                            pass\n",
    "                        position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                        temp_ask = temp_ask + 1\n",
    "                       \n",
    "                        order_book_ask = insert(order_book_ask,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)\n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if position_ == 0:\n",
    "                                 \n",
    "                                if order_book_ask[position_+1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]: \n",
    "                                    print 'Some error3(Ask & Q>0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])     \n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif 0 < position_< len(order_book_ask)-1:\n",
    "                                if order_book_ask[position_+1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]: \n",
    "                                    print 'Some error3(Ask & Q>0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])    \n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif position_ == len(order_book_ask)-1: \n",
    "                                if order_book_ask[position_-1:position_-1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]: \n",
    "                                    print 'Some error3(Ask & Q>0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])      \n",
    "                                    break\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif position_ == 0 and len(order_book_ask[0]) == 1:\n",
    "                                pass\n",
    "                        else:\n",
    "                            pass\n",
    "            elif int(data[['QuantityDifference']][i:i+1].values) < 0:    \n",
    "                if data.TimeStamp[i] == x1[temp_ask]: \n",
    "                    order_number_ =  data['OrderNumber'][i:i + 1].iloc[0]\n",
    "                    position_ = order_book_ask[order_book_ask.OrderNumber == order_number_].index[0]                \n",
    "                    price_ = data[i:i+1]['Price'].iloc[0]\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if position_ == 0 and len(order_book_ask) > 1:\n",
    "                            if order_book_ask[position_+1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:    \n",
    "                                print 'Some error4(Ask & Q<0 & timestamp not change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            else:                  \n",
    "                                pass\n",
    "                        elif 0 < position_< len(order_book_ask)-1:\n",
    "                            if order_book_ask[position_+1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:                             \n",
    "                                print 'Some error4(Ask & Q<0 & timestamp not change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            else:  \n",
    "                                pass \n",
    "                        elif position_ == len(order_book_ask)-1:\n",
    "                            if position_ > 0 and order_book_ask[position_-1:position_-1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]: \n",
    "                                print 'Some error4(Ask & Q<0 & timestamp not change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])   \n",
    "                                break\n",
    "                            elif position_ == 0:\n",
    "                                pass                        \n",
    "                            else:                     \n",
    "                                pass\n",
    "                        elif position_ == 0 and len(order_book_ask) == 1:    \n",
    "                            pass\n",
    "                    else:\n",
    "                        pass\n",
    "                    if order_book_ask[(order_book_ask.OrderNumber == order_number_)&(order_book_ask.Price == price_)]['QuantityDifference'].iloc[0] == abs(data[i:i+1]['QuantityDifference'].iloc[0]):\n",
    "                        order_book_ask = order_book_ask.drop(order_book_ask.index[[position_]]).reset_index(drop = True)                \n",
    "                    else:\n",
    "                        order_book_ask['QuantityDifference'][order_book_ask.OrderNumber == order_number_] = order_book_ask['QuantityDifference'][order_book_ask.OrderNumber == order_number_] + data[i:i+1]['QuantityDifference'].iloc[0]\n",
    "\n",
    "                elif data.TimeStamp[i] != x1[temp_ask]:\n",
    "                    \n",
    "                    order_book_bid_sum,order_book_ask_sum = order_book_to_csv(order_book_bid,order_book_ask,data,i)\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) > 0.03 or\\\n",
    "                        round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) < 0:\n",
    "                            if data[i-1:i].BidOrAsk.iloc[0] == 'A':\n",
    "                                if order_book_ask_sum[0:1].values.tolist()[0][1] == data[i-1:i].BestQuantity.iloc[0]:\n",
    "                                    pass\n",
    "                                else:\n",
    "                                    print 'Best ask quantity is false'\n",
    "                                    #break\n",
    "                                    pass\n",
    "                            else:\n",
    "                                j = i - 1\n",
    "                                while j >= 1:\n",
    "                                    if data[j-1:j].BidOrAsk.iloc[0] == 'A':\n",
    "                                        if order_book_ask_sum[0:1].values.tolist()[0][1] == data[j-1:j].BestQuantity.iloc[0]:\n",
    "                                            break\n",
    "                                    else:\n",
    "                                        j = j - 1\n",
    "                                        pass\n",
    "                    else:\n",
    "                        pass\n",
    "                    order_number_ =  data['OrderNumber'][i : i + 1].iloc[0]\n",
    "                    position_ = order_book_ask[order_book_ask.OrderNumber == order_number_].index[0]\n",
    "                    price_ = data[i:i+1]['Price'].iloc[0]\n",
    "                    temp_ask = temp_ask + 1\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if position_ == 0 and len(order_book_ask) > 1:\n",
    "                            if order_book_ask[position_ + 1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:\n",
    "                                print 'Some error5(Ask & Q<0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])    \n",
    "                                break\n",
    "                            else:\n",
    "                                pass\n",
    "                        elif 0 < position_< len(order_book_ask)-1:    \n",
    "                            if order_book_ask[position_ + 1:position_+1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:                             \n",
    "                                print 'Some error5(Ask & Q<0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])     \n",
    "                                break\n",
    "                            else:\n",
    "                                pass\n",
    "                        elif position_ == len(order_book_ask)-1:\n",
    "                            if position_ > 0 and order_book_ask[position_-1:position_-1 + 1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]: \n",
    "                                print 'Some error5(Ask & Q<0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])      \n",
    "                                break\n",
    "                            elif position_ == 0:\n",
    "                                pass                       \n",
    "                            else:                       \n",
    "                                pass\n",
    "                        elif position_ == 0 and len(order_book_ask) == 1:\n",
    "                            pass      \n",
    "                    else:\n",
    "                        pass\n",
    "                    if order_book_ask[(order_book_ask.OrderNumber == order_number_)&(order_book_ask.Price == price_)]['QuantityDifference'].iloc[0] == abs(data[i:i+1]['QuantityDifference'].iloc[0]):                    \n",
    "                        order_book_ask = order_book_ask.drop(order_book_ask.index[[position_]]).reset_index(drop = True)\n",
    "                    else:\n",
    "                        order_book_ask['QuantityDifference'][order_book_ask.OrderNumber == order_number_] = order_book_ask['QuantityDifference'][order_book_ask.OrderNumber == order_number_] + data[i:i+1]['QuantityDifference'].iloc[0]\n",
    "\n",
    "        elif data.BidOrAsk[i] == 'B':\n",
    "            data_bid_Quantity = data.BestQuantity[i]\n",
    "            if int(data[['QuantityDifference']][i:i+1].values) > 0: \n",
    "               \n",
    "                if order_book_ask.Price[0] <= data[i:i+1].Price.iloc[0] and time_second < 32400:\n",
    "                    for k in range(0,len(order_book_ask)):\n",
    "                        diff = order_book_ask.QuantityDifference_[k] - data[i:i+1].QuantityDifference_.iloc[0]\n",
    "                        if order_book_ask.Price[k] <= data[i:i+1].Price.iloc[0] and diff >= 0:\n",
    "                            order_book_ask.QuantityDifference_[k] = diff\n",
    "                            data[i:i+1].QuantityDifference_.iloc[0] = 0\n",
    "                            break\n",
    "                        elif order_book_ask.Price[k] <= data[i:i+1].Price.iloc[0] and diff < 0:\n",
    "                            order_book_ask.QuantityDifference_[k] = 0\n",
    "                            data[i:i+1].QuantityDifference_.iloc[0] = - diff\n",
    "                            pass\n",
    "                        else:\n",
    "                            break\n",
    "                if data.TimeStamp[i] == x2[temp_bid]:\n",
    "                    position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                    order_book_bid = insert(order_book_bid,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)                \n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if position_ == 0 and len(order_book_bid) > 1:              \n",
    "                            if order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                                            \n",
    "                                print 'Some error1(Bid & Q>0 & timestamp not change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            else:                    \n",
    "                                pass \n",
    "                        elif 0 < position_< len(order_book_bid)-1: \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                             \n",
    "                                print 'Some error1(Bid & Q>0 & timestamp not change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break    \n",
    "                            else:   \n",
    "                                pass   \n",
    "                        elif position_ == len(order_book_bid)-1 and len(order_book_bid) > 1:   \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:                        \n",
    "                                print 'Some error1(Bid & Q>0 & timestamp not change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            else:                \n",
    "                                pass\n",
    "                        elif position_ == 0 and len(order_book_bid[temp_bid]) == 1:\n",
    "                            pass \n",
    "                    else:\n",
    "                        pass\n",
    "                elif data.TimeStamp[i] != x2[temp_bid]:\n",
    "                    if temp_bid == 0:\n",
    "                        best_price = data[i:(i+1)]['BestPrice']             \n",
    "                        position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                        temp_bid = temp_bid + 1                 \n",
    "                        order_book_bid = insert(order_book_bid,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)                                     \n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if position_ == 0  and len(order_book_bid) > 1:\n",
    "                                if order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0] or order_book_bid['Price'][0:1].iloc[0] != data['BestPrice'][i]:                                \n",
    "                                    print 'Some error2(Bid & Q>0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0]) + data['OrderNumber'][i:i+1].iloc[0]\n",
    "                                    break\n",
    "                                else:                   \n",
    "                                    pass      \n",
    "                            elif 0 < position_< len(order_book_bid)-1:    \n",
    "                                if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                                \n",
    "                                    print 'Some error2(Bid & Q>0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                    break\n",
    "                                else: \n",
    "                                    pass\n",
    "                            elif position_ == len(order_book_bid)-1:\n",
    "                                if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid['Price'][0:1].iloc[0] != data['BestPrice'][i]:                                \n",
    "                                    print 'Some error2(Bid & Q>0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])                                        \n",
    "                                    pass\n",
    "                                else:\n",
    "                                    pass\n",
    "                            elif position_ == 0 and len(order_book_bid) == 1: \n",
    "                                    pass\n",
    "                        else:\n",
    "                            pass\n",
    "                    else:\n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) > 0.03 or\\\n",
    "                            round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) < 0:\n",
    "                                order_book_bid_sum,order_book_ask_sum = order_book_to_csv(order_book_bid,order_book_ask,data,i)                    \n",
    "                                if data[i-1:i].BidOrAsk.iloc[0] == 'B':\n",
    "                                    if order_book_bid_sum[0:1].values.tolist()[0][1] == data[i-1:i].BestQuantity.iloc[0]:\n",
    "                                        pass\n",
    "                                    else:\n",
    "                                        print 'Best bid quantity is false'\n",
    "                                        #break\n",
    "                                        pass\n",
    "                                else:\n",
    "                                    j = i - 1\n",
    "                                    while j >= 1:\n",
    "                                        if data[j-1:j].BidOrAsk.iloc[0] == 'B':\n",
    "                                            if order_book_bid_sum[0:1].values.tolist()[0][1] == data[j-1:j].BestQuantity.iloc[0]:\n",
    "                                                break\n",
    "                                            else:\n",
    "                                                print 'Best bid quantity is false'\n",
    "                                                #break\n",
    "                                                pass\n",
    "                                        else:\n",
    "                                            j = j - 1\n",
    "                                            pass\n",
    "                        else:\n",
    "                            pass\n",
    "                        position_ = int(data[['OrderBookPosition']][i:i+1].iloc[0]) - 1 \n",
    "                        temp_bid = temp_bid + 1 \n",
    "                        order_book_bid = insert(order_book_bid,data[['Price','OrderNumber','QuantityDifference','QuantityDifference_']][i:i+1],position_)                       \n",
    "                        if time_second > 32400 and time_second < 57300:\n",
    "                            if position_ == 0 and len(order_book_bid) > 1:\n",
    "                                if order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0] or order_book_bid['Price'][0:1].iloc[0] != data['BestPrice'][i]:                                \n",
    "                                    print 'Some error3(Bid & Q>0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])     \n",
    "                                    break\n",
    "                                else:                    \n",
    "                                    pass\n",
    "                            elif 0 < position_< len(order_book_bid)-1:    \n",
    "                                if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                                \n",
    "                                    print 'Some error3(Bid & Q>0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                    break\n",
    "                                else: \n",
    "                                    pass\n",
    "                            elif position_ == len(order_book_bid)-1:   \n",
    "                                if order_book_bid[position_-1:position_-1 + 1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid['Price'][0:1].iloc[0] != data['BestPrice'][i]:                                \n",
    "                                    print 'Some error3(Bid & Q>0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])      \n",
    "                                    break\n",
    "                                else:                      \n",
    "                                    pass\n",
    "                            elif position_ == 0 and len(order_book_bid[0]) == 1:\n",
    "                                pass\n",
    "                        else:\n",
    "                            pass\n",
    "            elif int(data[['QuantityDifference']][i:i+1].values) < 0:    \n",
    "                if data.TimeStamp[i] == x2[temp_bid]: \n",
    "                    order_number_ =  data['OrderNumber'][i : i + 1].iloc[0]\n",
    "                    position_ = order_book_bid[order_book_bid.OrderNumber == order_number_].index[0]                \n",
    "                    price_ = data[i:i+1]['Price'].iloc[0]\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if position_ == 0 and len(order_book_bid) > 1:    \n",
    "                            if order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print 'Some error4(Bid & Q<0 & timestamp not change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            else:                     \n",
    "                                pass               \n",
    "                        elif 0 < position_< len(order_book_bid)-1:      \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print 'Some error4(Bid & Q<0 & timestamp not change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            else: \n",
    "                                pass\n",
    "                        elif position_ == len(order_book_bid)-1:    \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print 'Some error4(Bid & Q<0 & timestamp not change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])      \n",
    "                                break\n",
    "                            elif position_ == 0:\n",
    "                                pass\n",
    "                            else:                        \n",
    "                                pass\n",
    "                        elif position_ == 0 and len(order_book_bid) == 1:\n",
    "                            pass\n",
    "                    else:\n",
    "                        pass\n",
    "                    if order_book_bid[(order_book_bid.OrderNumber == order_number_)&(order_book_bid.Price == price_)]['QuantityDifference'].iloc[0] == abs(data[i:i+1]['QuantityDifference'].iloc[0]):                    \n",
    "                        order_book_bid = order_book_bid.drop(order_book_bid.index[[position_]]).reset_index(drop = True)                \n",
    "                    else:\n",
    "                        order_book_bid['QuantityDifference'][order_book_bid.OrderNumber == order_number_] = order_book_bid['QuantityDifference'][order_book_bid.OrderNumber == order_number_] + data[i:i+1]['QuantityDifference'].iloc[0]                            \n",
    "                elif data.TimeStamp[i] != x2[temp_bid]:\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) > 0.03 or\\\n",
    "                        round(float(data[i:i+1].TimeStamp.iloc[0][18:29]) - float(data[i-1:i].TimeStamp.iloc[0][18:28]),4) < 0:\n",
    "                            order_book_bid_sum,order_book_ask_sum = order_book_to_csv(order_book_bid,order_book_ask,data,i)\n",
    "                            if data[i-1:i].BidOrAsk.iloc[0] == 'B':\n",
    "                                if order_book_bid_sum[0:1].values.tolist()[0][1] == data[i-1:i].BestQuantity.iloc[0]:\n",
    "                                    pass\n",
    "                                else:\n",
    "                                    print 'Best bid quantity is false'\n",
    "                                    #break\n",
    "                                    pass\n",
    "                            else:\n",
    "                                j = i - 1\n",
    "                                while j >= 1:\n",
    "                                    if data[j-1:j].BidOrAsk.iloc[0] == 'B':\n",
    "                                        if order_book_bid_sum[0:1].values.tolist()[0][1] == data[j-1:j].BestQuantity.iloc[0]:\n",
    "                                            break\n",
    "                                        else:\n",
    "                                            print 'Best bid quantity is false'\n",
    "                                            #break\n",
    "                                            pass\n",
    "                                    else:\n",
    "                                        j = j - 1\n",
    "                                        pass\n",
    "                    else:\n",
    "                        pass\n",
    "                    order_number_ =  data['OrderNumber'][i:i+1].iloc[0]\n",
    "                    position_ = order_book_bid[order_book_bid.OrderNumber == order_number_].index[0]\n",
    "                    price_ = data[i:i+1]['Price'].iloc[0]\n",
    "                    temp_bid = temp_bid + 1\n",
    "                    if time_second > 32400 and time_second < 57300:\n",
    "                        if position_ == 0 and len(order_book_bid) > 1:   \n",
    "                            if order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print 'Some error5(Bid & Q<0 & timestamp change & 1),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            else:                   \n",
    "                                pass   \n",
    "                        elif 0 < position_< len(order_book_bid)-1:    \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0] or order_book_bid[position_+1:position_+1+1][\"Price\"].iloc[0] > data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print 'Some error5(Bid & Q<0 & timestamp change & 2),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            else:  \n",
    "                                pass\n",
    "                        elif position_ == len(order_book_bid)-1:    \n",
    "                            if order_book_bid[position_-1:position_-1+1][\"Price\"].iloc[0] < data['Price'][i:i+1].iloc[0]:                            \n",
    "                                print 'Some error5(Bid & Q<0 & timestamp change & 3),position = %d,index = %d,price = %d,OrderNumber = %s'%(position_,i,data['Price'][i:i+1].iloc[0],data['OrderNumber'][i:i+1].iloc[0])\n",
    "                                break\n",
    "                            elif position_ == 0:\n",
    "                                pass\n",
    "                            else:                      \n",
    "                                pass    \n",
    "                        elif position_ == 0 and len(order_book_bid) == 1:\n",
    "                            pass  \n",
    "                    else:\n",
    "                        pass\n",
    "                    if order_book_bid[(order_book_bid.OrderNumber == order_number_)&(order_book_bid.Price == price_)]['QuantityDifference'].iloc[0] == abs(data[i:i+1]['QuantityDifference'].iloc[0]):\n",
    "                        order_book_bid = order_book_bid.drop(order_book_bid.index[[position_]]).reset_index(drop = True)                \n",
    "                    else:\n",
    "                        order_book_bid['QuantityDifference'][order_book_bid.OrderNumber == order_number_] = order_book_bid['QuantityDifference'][order_book_bid.OrderNumber == order_number_] + data[i:i+1]['QuantityDifference'].iloc[0]\n",
    "    return data, order_book_bid, order_book_ask #, order_book_bid_sum, order_book_ask_sum\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    \n",
    "    path = '/home/rory/Demo/Data_Transformation/message_data/CN_Futures_'\n",
    "    year = 2014\n",
    "    best_price_number = 3\n",
    "    \n",
    "    # Future Delivery Months 近月合約代碼 : series\n",
    "    # 2014 :CNF14:1月, CNG14:2月, CNH14:3月, CNJ14:4月, CNK14:5月, CNM14:6月,\n",
    "    #       CNN14:7月, CNQ14:8月, CNU14:9月, CNV14:10月, CNX14:11月, CNZ14:12月\n",
    "    # 2015 :CNF15:1月, CNG15:2月, CNH15:3月, CNJ15:4月, CNK15:5月, CNM15:6月,\n",
    "    #       CNN15:7月, CNQ15:8月, CNU15:9月, CNV15:10月, CNX15:11月, CNZ15:12月\n",
    "        \n",
    "    series = 'CNF14' \n",
    "    month = 1\n",
    "    day_ = [2]\n",
    "    for i in day_:\n",
    "        print i\n",
    "        start = time.time()\n",
    "        data, order_book_bid, order_book_ask = order_book_tranform(year,month,i,path,best_price_number,series)\n",
    "        end = time.time()\n",
    "        print \"Total time = %f\"%(end - start)    \n",
    "    \n",
    "        \n",
    "        \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Series</th>\n",
       "      <th>SequenceNumber</th>\n",
       "      <th>TimeStamp</th>\n",
       "      <th>OrderNumber</th>\n",
       "      <th>OrderBookPosition</th>\n",
       "      <th>Price</th>\n",
       "      <th>QuantityDifference</th>\n",
       "      <th>Trade</th>\n",
       "      <th>BidOrAsk</th>\n",
       "      <th>BestPrice</th>\n",
       "      <th>BestQuantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>642F9A8039E51EE5</td>\n",
       "      <td>1</td>\n",
       "      <td>757000</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>715500</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>2</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>490D725B88E56DE5</td>\n",
       "      <td>1</td>\n",
       "      <td>755000</td>\n",
       "      <td>10</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>715500</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>3</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>E1C9F25394A679A6</td>\n",
       "      <td>1</td>\n",
       "      <td>719500</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>715500</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>4</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>ECA71A88142AF92B</td>\n",
       "      <td>4</td>\n",
       "      <td>768500</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>715500</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>5</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>E325782FEB07CE67</td>\n",
       "      <td>1</td>\n",
       "      <td>700500</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>B</td>\n",
       "      <td>700500</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>6</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>4959765357F03CF0</td>\n",
       "      <td>2</td>\n",
       "      <td>699000</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>B</td>\n",
       "      <td>700500</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>7</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>E8ABBA801F3C015C</td>\n",
       "      <td>2</td>\n",
       "      <td>740000</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>715500</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>8</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>CBADF86F77EA578A</td>\n",
       "      <td>1</td>\n",
       "      <td>717500</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>715500</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>9</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>B3F5144E612645E6</td>\n",
       "      <td>3</td>\n",
       "      <td>690500</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>B</td>\n",
       "      <td>700500</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CNF14</td>\n",
       "      <td>10</td>\n",
       "      <td>2014-01-02D04:19:51.857166800</td>\n",
       "      <td>1335141E47F92CF9</td>\n",
       "      <td>4</td>\n",
       "      <td>682000</td>\n",
       "      <td>100</td>\n",
       "      <td></td>\n",
       "      <td>B</td>\n",
       "      <td>700500</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Series  SequenceNumber                      TimeStamp       OrderNumber  \\\n",
       "0  CNF14               1  2014-01-02D04:19:51.857166800  642F9A8039E51EE5   \n",
       "1  CNF14               2  2014-01-02D04:19:51.857166800  490D725B88E56DE5   \n",
       "2  CNF14               3  2014-01-02D04:19:51.857166800  E1C9F25394A679A6   \n",
       "3  CNF14               4  2014-01-02D04:19:51.857166800  ECA71A88142AF92B   \n",
       "4  CNF14               5  2014-01-02D04:19:51.857166800  E325782FEB07CE67   \n",
       "5  CNF14               6  2014-01-02D04:19:51.857166800  4959765357F03CF0   \n",
       "6  CNF14               7  2014-01-02D04:19:51.857166800  E8ABBA801F3C015C   \n",
       "7  CNF14               8  2014-01-02D04:19:51.857166800  CBADF86F77EA578A   \n",
       "8  CNF14               9  2014-01-02D04:19:51.857166800  B3F5144E612645E6   \n",
       "9  CNF14              10  2014-01-02D04:19:51.857166800  1335141E47F92CF9   \n",
       "\n",
       "   OrderBookPosition   Price  QuantityDifference Trade BidOrAsk  BestPrice  \\\n",
       "0                  1  757000                   3              A     715500   \n",
       "1                  1  755000                  10              A     715500   \n",
       "2                  1  719500                   1              A     715500   \n",
       "3                  4  768500                   1              A     715500   \n",
       "4                  1  700500                   2              B     700500   \n",
       "5                  2  699000                   2              B     700500   \n",
       "6                  2  740000                   2              A     715500   \n",
       "7                  1  717500                   1              A     715500   \n",
       "8                  3  690500                   1              B     700500   \n",
       "9                  4  682000                 100              B     700500   \n",
       "\n",
       "   BestQuantity  \n",
       "0             2  \n",
       "1             2  \n",
       "2             2  \n",
       "3             2  \n",
       "4             2  \n",
       "5             2  \n",
       "6             2  \n",
       "7             2  \n",
       "8             2  \n",
       "9             2  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('/home/rory/Demo/Data_Transformation/message_data/CN_Futures_2014.01.02.csv').head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
